package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.proactive.webapp.dao.ResourceCollectDAO;
import com.proactive.webapp.entity.Resource;
import com.proactive.webapp.entity.ResourceCollect;

public class ResourceCollectDAOImpl implements ResourceCollectDAO{
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void insert(ResourceCollect resourceCollect) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO resource_collect(resourceID, memberAccount, createDate) VALUES(?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, resourceCollect.getRid());
			smt.setString(2, resourceCollect.getAccount());
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public void delete(ResourceCollect resourceCollect) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM resource_collect WHERE resourceID=? AND memberAccount=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, resourceCollect.getRid());
			smt.setString(2, resourceCollect.getAccount());
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public int countTotal(String account) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID HAVING COUNT(*)<10) "
				+ "AS reportCount FROM resource_collect a JOIN resource c ON a.resourceID = c.resourceID "
				+ "WHERE a.memberAccount = ? AND resourceStatus = 1 HAVING reportCount IS NOT NULL";
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Resource> getList(String loginAccount, String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Resource> resourceList = new ArrayList<Resource>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID HAVING COUNT(*)<10) "
				+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
				+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
				+ "AS report FROM resource_collect a JOIN resource e ON a.resourceID = e.resourceID "
				+ "WHERE a.memberAccount = ? AND resourceStatus = 1 HAVING reportCount IS NOT NULL "
				+ "ORDER BY a.createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setString(3, account);
			smt.setInt(4, start);
			smt.setInt(5, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Resource resource = new Resource();
				resource.setId(rs.getInt("resourceID"));
				resource.setTitle(rs.getString("resourceTitle"));
				resource.setContent(rs.getString("resourceContent"));
				resource.setUrl(rs.getString("resourceUrl"));
				resource.setImagePath(rs.getString("resourceImage"));
				resource.setViewCount(rs.getInt("resourceViewCount"));
				resource.setReportFlag(rs.getInt("report")==0?false:true);
				resource.setCollectFlag(rs.getInt("collect")==0?false:true);
				resource.setCollectString(resource.isCollectFlag()?"已收藏":"收藏");
				resource.setCollectString1(resource.isCollectFlag()?"none":"inline");
				resource.setCollectString2(resource.isCollectFlag()?"inline":"none");
				resource.setCollectClass(resource.isCollectFlag()?"topfocusCollect":"");
				resourceList.add(resource);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return resourceList;
	}
}
